# -*- coding: utf-8 -*-
"""
Excel处理模块 - 导入导出功能
"""

import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
import os
from config import EXPORT_DIR, IP_EXPORT_FILE, APP_SYSTEM_EXPORT_FILE, ACCOUNT_SYSTEM_EXPORT_FILE
from utils import ensure_dir
from models import IPManagementModel, AssetInfoModel, MiddlewareInfoModel, AppSystemModel, OSAccountModel

class ExcelHandler:
    def __init__(self):
        self.ip_model = IPManagementModel()
        self.asset_model = AssetInfoModel()
        self.middleware_model = MiddlewareInfoModel()
        self.app_system_model = AppSystemModel()
        self.os_account_model = OSAccountModel()
        
        # 确保导出目录存在
        self.export_dir = ensure_dir(EXPORT_DIR)
    
    def create_ip_template(self):
        """创建IP管理Excel模板"""
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "IP管理"
        
        # 设置表头
        headers = [
            "*IP地址", "*设备类型", "*部门", "*用途描述", 
            "关联资产编号", "负责人", "网段"
        ]
        
        # 设置表头样式
        header_font = Font(bold=True, color="FFFFFF")
        header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
        header_alignment = Alignment(horizontal="center", vertical="center")
        
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_alignment
        
        # 设置列宽
        column_widths = [15, 12, 15, 20, 15, 12, 12]
        for i, width in enumerate(column_widths, 1):
            ws.column_dimensions[get_column_letter(i)].width = width
        
        # 添加说明行
        ws.cell(row=2, column=1, value="说明：带*号字段为必填项")
        ws.cell(row=3, column=1, value="设备类型可选：服务器、安全设备、pc机")
        ws.cell(row=4, column=1, value="网段可选：服务器网段、办公网段、临床网段")
        
        template_path = os.path.join(self.export_dir, "IP_template.xlsx")
        wb.save(template_path)
        return template_path
    
    def create_asset_template(self):
        """创建资产信息Excel模板"""
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "资产信息"
        
        headers = [
            "*资产编号", "*设备类型", "型号", "入账日期", 
            "CPU核数", "内存GB", "磁盘GB", "虚拟化类型"
        ]
        
        # 设置表头样式
        header_font = Font(bold=True, color="FFFFFF")
        header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
        header_alignment = Alignment(horizontal="center", vertical="center")
        
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_alignment
        
        # 设置列宽
        column_widths = [15, 12, 15, 12, 10, 10, 10, 12]
        for i, width in enumerate(column_widths, 1):
            ws.column_dimensions[get_column_letter(i)].width = width
        
        # 添加说明行
        ws.cell(row=2, column=1, value="说明：带*号字段为必填项")
        ws.cell(row=3, column=1, value="虚拟化类型可选：VMware、KVM")
        
        template_path = os.path.join(self.export_dir, "asset_template.xlsx")
        wb.save(template_path)
        return template_path
    
    def create_middleware_template(self):
        """创建中间件Excel模板"""
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "中间件信息"
        
        headers = [
            "*名称", "*类型", "版本号", "安装目录", 
            "端口", "关联IP", "关联业务系统"
        ]
        
        # 设置表头样式
        header_font = Font(bold=True, color="FFFFFF")
        header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
        header_alignment = Alignment(horizontal="center", vertical="center")
        
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_alignment
        
        # 设置列宽
        column_widths = [15, 12, 12, 20, 10, 15, 20]
        for i, width in enumerate(column_widths, 1):
            ws.column_dimensions[get_column_letter(i)].width = width
        
        # 添加说明行
        ws.cell(row=2, column=1, value="说明：带*号字段为必填项")
        ws.cell(row=3, column=1, value="类型可选：数据库、缓存、web发布")
        ws.cell(row=4, column=1, value="名称可选：Nginx、MySQL、Redis、Tomcat等")
        
        template_path = os.path.join(self.export_dir, "middleware_template.xlsx")
        wb.save(template_path)
        return template_path
    
    def create_app_system_template(self):
        """创建应用系统Excel模板"""
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "应用系统"
        
        headers = [
            "*系统名称", "登录地址", "管理员账号", "管理员密码", 
            "所属IP", "系统类型"
        ]
        
        # 设置表头样式
        header_font = Font(bold=True, color="FFFFFF")
        header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
        header_alignment = Alignment(horizontal="center", vertical="center")
        
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_alignment
        
        # 设置列宽
        column_widths = [20, 25, 15, 15, 15, 12]
        for i, width in enumerate(column_widths, 1):
            ws.column_dimensions[get_column_letter(i)].width = width
        
        # 添加说明行
        ws.cell(row=2, column=1, value="说明：带*号字段为必填项")
        ws.cell(row=3, column=1, value="系统类型可选：办公、临时、管理")
        
        template_path = os.path.join(self.export_dir, "app_system_template.xlsx")
        wb.save(template_path)
        return template_path
    
    def create_os_account_template(self):
        """创建操作系统账号Excel模板"""
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "系统账号"
        
        headers = [
            "*账户名称", "*账号级别", "*账号类型", "账号密码", "关联IP"
        ]
        
        # 设置表头样式
        header_font = Font(bold=True, color="FFFFFF")
        header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
        header_alignment = Alignment(horizontal="center", vertical="center")
        
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_alignment
        
        # 设置列宽
        column_widths = [15, 12, 12, 15, 15]
        for i, width in enumerate(column_widths, 1):
            ws.column_dimensions[get_column_letter(i)].width = width
        
        # 添加说明行
        ws.cell(row=2, column=1, value="说明：带*号字段为必填项")
        ws.cell(row=3, column=1, value="账号级别可选：管理员、普通用户")
        ws.cell(row=4, column=1, value="账号类型可选：共享账号、单独账号")
        ws.cell(row=5, column=1, value="注意：共享账号可关联多个IP，单独账号仅关联1个IP")
        
        template_path = os.path.join(self.export_dir, "os_account_template.xlsx")
        wb.save(template_path)
        return template_path
    
    def export_ip_data(self):
        """导出IP数据"""
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "IP管理"
        
        # 设置表头
        headers = [
            "IP地址", "设备类型", "部门", "用途描述", 
            "关联资产编号", "负责人", "网段", "创建时间", "更新时间"
        ]
        
        for col, header in enumerate(headers, 1):
            ws.cell(row=1, column=col, value=header)
        
        # 获取数据
        ip_data = self.ip_model.get_all_ips()
        
        # 写入数据
        for row, data in enumerate(ip_data, 2):
            ws.cell(row=row, column=1, value=data['ip_address'])
            ws.cell(row=row, column=2, value=data['device_type'])
            ws.cell(row=row, column=3, value=data['department'])
            ws.cell(row=row, column=4, value=data['purpose'])
            ws.cell(row=row, column=5, value=data['asset_id'])
            ws.cell(row=row, column=6, value=data['responsible_person'])
            ws.cell(row=row, column=7, value=data['network_segment'])
            ws.cell(row=row, column=8, value=data['created_time'])
            ws.cell(row=row, column=9, value=data['updated_time'])
        
        # 设置列宽
        for i in range(1, len(headers) + 1):
            ws.column_dimensions[get_column_letter(i)].width = 15
        
        export_path = os.path.join(self.export_dir, IP_EXPORT_FILE)
        wb.save(export_path)
        return export_path
    
    def export_app_system_data(self):
        """导出应用系统数据"""
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "应用系统"
        
        headers = [
            "系统名称", "登录地址", "管理员账号", "管理员密码", 
            "所属IP", "系统类型", "创建时间", "更新时间"
        ]
        
        for col, header in enumerate(headers, 1):
            ws.cell(row=1, column=col, value=header)
        
        # 获取数据
        app_data = self.app_system_model.get_all_app_systems()
        
        # 写入数据
        for row, data in enumerate(app_data, 2):
            ws.cell(row=row, column=1, value=data['system_name'])
            ws.cell(row=row, column=2, value=data['login_url'])
            ws.cell(row=row, column=3, value=data['admin_account'])
            ws.cell(row=row, column=4, value=data['admin_password'])
            ws.cell(row=row, column=5, value=data['associated_ip'])
            ws.cell(row=row, column=6, value=data['system_type'])
            ws.cell(row=row, column=7, value=data['created_time'])
            ws.cell(row=row, column=8, value=data['updated_time'])
        
        # 设置列宽
        for i in range(1, len(headers) + 1):
            ws.column_dimensions[get_column_letter(i)].width = 15
        
        export_path = os.path.join(self.export_dir, APP_SYSTEM_EXPORT_FILE)
        wb.save(export_path)
        return export_path
    
    def export_os_account_data(self):
        """导出操作系统账号数据"""
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "系统账号"
        
        headers = [
            "账户名称", "账号级别", "账号类型", "账号密码", 
            "关联IP", "创建时间", "更新时间"
        ]
        
        for col, header in enumerate(headers, 1):
            ws.cell(row=1, column=col, value=header)
        
        # 获取数据
        account_data = self.os_account_model.get_all_os_accounts()
        
        # 写入数据
        for row, data in enumerate(account_data, 2):
            ws.cell(row=row, column=1, value=data['account_name'])
            ws.cell(row=row, column=2, value=data['account_level'])
            ws.cell(row=row, column=3, value=data['account_type'])
            ws.cell(row=row, column=4, value=data['account_password'])
            ws.cell(row=row, column=5, value=data['associated_ip'])
            ws.cell(row=row, column=6, value=data['created_time'])
            ws.cell(row=row, column=7, value=data['updated_time'])
        
        # 设置列宽
        for i in range(1, len(headers) + 1):
            ws.column_dimensions[get_column_letter(i)].width = 15
        
        export_path = os.path.join(self.export_dir, ACCOUNT_SYSTEM_EXPORT_FILE)
        wb.save(export_path)
        return export_path
    
    def import_ip_data(self, file_path):
        """导入IP数据"""
        wb = openpyxl.load_workbook(file_path)
        ws = wb.active
        
        success_count = 0
        error_messages = []
        
        # 跳过表头和说明行，从第5行开始读取数据
        for row in range(5, ws.max_row + 1):
            try:
                ip_address = ws.cell(row=row, column=1).value
                device_type = ws.cell(row=row, column=2).value
                department = ws.cell(row=row, column=3).value
                purpose = ws.cell(row=row, column=4).value
                asset_id = ws.cell(row=row, column=5).value
                responsible_person = ws.cell(row=row, column=6).value
                network_segment = ws.cell(row=row, column=7).value
                
                # 验证必填字段
                if not all([ip_address, device_type, department, purpose]):
                    error_messages.append(f"第{row}行：必填字段不能为空")
                    continue
                
                self.ip_model.add_ip(
                    ip_address=ip_address,
                    device_type=device_type,
                    department=department,
                    purpose=purpose,
                    asset_id=asset_id,
                    responsible_person=responsible_person,
                    network_segment=network_segment
                )
                success_count += 1
                
            except Exception as e:
                error_messages.append(f"第{row}行：{str(e)}")
        
        return success_count, error_messages